﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Security;
//using System.Data.Linq; 

namespace GameSchool.Models
{
    public class GameSchoolRepository : IGameSchoolRepository
    {
        GameSchoolContext db = new GameSchoolContext();


        #region Achievements

        public IQueryable<Achievement> Achievements()
        {
            return db.achievements;
        }

        public IEnumerable<Achievement> GetAchievements()
        {
            var result = from t in db.achievements
                         orderby t.type ascending //Check this condition - Oli.
                         select t;
            return result;
        }

        public Achievement GetAchievements(int id)
        {
            var result = (from t in db.achievements
                          where t.achievementID == id
                          select t).SingleOrDefault();
            return result;
        }

        public IEnumerable<Achievement> GetAchievementsForStudent(Student theStudent)
        {
            var achievements = db.achievementCourseStudents.Where(acs => acs.studentID == theStudent.studentID).Select(acs => acs.achievement);

            return achievements;
        }

        public void AddAchievement(Achievement theAchievement)
        {
            db.achievements.Add(theAchievement);
        }

        IEnumerable<Achievement> IGameSchoolRepository.GetAchievements(int id)
        {
            return db.achievements;
        }

        public void UpdateAchievement(int id)
        {
            // do this if we have time - eiki
            throw new NotImplementedException();
        }

        public void DeleteAchievement(int id)
        {
            // do this if we have time - eiki
            throw new NotImplementedException();
        }

        #endregion

        #region Students

        public IQueryable<Student> Students()
        {
            return db.students;
        }

        public IEnumerable<Student> GetStudents()
        {
            var result = from t in db.students
                         orderby t.name ascending
                         select t;
            return result;
        }

        public Student GetStudents(int id)
        {
            var result = (from t in db.students
                          where t.studentID == id
                          select t).SingleOrDefault();
            return result;
        }

        public void AddStudent(Student theStudent)
        {
            db.students.Add(theStudent);
        }
        IEnumerable<Student> IGameSchoolRepository.GetStudents(int id)
        {
            throw new NotImplementedException();
        }

        /// <summary>
        /// Gets a students relationship with their login username and password.
        /// </summary>
        /// <returns></returns>
        public Student GetStudentLoginRelationship()
        {
            Guid userGuid = (Guid)Membership.GetUser().ProviderUserKey;
            var result = (from s in db.students
                          where s.userNameID == userGuid
                          select s).SingleOrDefault();
            return result;
        }

        public void UpdateStudent(int id)
        {
            /* I think I'll figure this out laterrrr.. -eiki
            using (this.db)
            {
                Student stu = db.students.Find(id);

                throw new NotImplementedException();
            }
             */
        }

        public void DeleteStudent(int id)
        {
            //later - eiki
            throw new NotImplementedException();
        }


        #endregion

        #region Courses

        /*  Courses repositories */
        public IQueryable<Course> Courses()
        {
            return db.courses;
        }

        // Ólöf:
        public IEnumerable<Course> GetCourses()
        {   
            var result = from c in db.courses
                         orderby c.title ascending
                         select c;
            return result;
        }

        public IEnumerable<Course> GetCourses(int id)
        {
            var result = from c in db.courses
                         where c.courseID == id
                         select c;
            return result;
        }

        public Course GetCourseByID(int id)  // I guess we need int? id
        {
            var result = (from c in db.courses
                          where c.courseID == id
                          select c).SingleOrDefault();

            return result;
        }

        public void AddCourse(Course theCourse)
        {
            db.courses.Add(theCourse);
        }

        public IEnumerable<Student> GetStudentsInCourse(Course theCourse)
        {
            var studentIDs = db.studentCourses.Where(c => c.courseID == theCourse.courseID).Select(c => c.studentID);

            var students = (from s in db.students
                            from sid in studentIDs
                            where s.studentID == sid
                            select s);
            return students;
        }

        public IEnumerable<Course> GetCoursesByStudent(Student theStudent) // Ólöf
        {
            var courseIDs = db.studentCourses.Where(s => s.studentID == theStudent.studentID).Select(s => s.courseID);

            //Needed to not return null - It does
            var courses = (from c in db.courses
                           from s in courseIDs
                           where c.courseID == s
                           select c);

            return courses;
        }

        public IEnumerable<Course> GetCoursesByTeacher(Teacher theTeacher)
        {
            var courseIDs = db.teacherCourses.Where(t => t.teacherID == theTeacher.teacherID).Select(t => t.courseID);

            //Needed to not return null
            var courses = (from c in db.courses
                           from t in courseIDs
                           where c.courseID == t
                           select c);

            return courses;
        }

        public Course GetCourseByLevel(Level theLevel)
        {
            var course = (from c in db.courses
                          where c.courseID == theLevel.courseID
                          select c).SingleOrDefault();

            return course;
        }

        public void AddCourseNumStudents(Course theCourse, int numStudents)
        {
            theCourse.numStudents = numStudents;

            db.SaveChanges();
        }
        public void UpdateCourse(int id)
        {
            // TODO later if we have time
        }

        public void DeleteCourse(int id)
        {
            // TODO later if we have time
        }

        #endregion

        #region Teachers

        /*   Teacher repository   */
        public IQueryable<Teacher> Teachers()
        {
            return db.teachers;
        }

        public IEnumerable<Teacher> GetTeachers()
        {
            var result = from t in db.teachers
                         orderby t.name ascending
                         select t;
            return result;
        }

        public Teacher GetTeachers(int id)
        {
            var result = (from t in db.teachers
                          where t.teacherID == id
                          select t).SingleOrDefault();
            return result;
        }

        public void AddTeacher(Teacher theTeacher)
        {
            db.teachers.Add(theTeacher);
        }

        public Teacher GetTeacherLoginRelationship()
        {
            Guid userGuid = (Guid)Membership.GetUser().ProviderUserKey;
            var resultID = (from t in db.teachers
                            where t.userNameID == userGuid
                            select t.teacherID).SingleOrDefault();

            var result = (from t in db.teachers
                          where t.teacherID == resultID
                          select t).SingleOrDefault();
            return result;
        }

        public IEnumerable<Teacher> GetTeachersInCourse(Course theCourse)
        {
            var teacherIDs = db.teacherCourses.Where(c => c.courseID == theCourse.courseID).Select(c => c.teacherID);

            var teachers = (from t in db.teachers
                            from tid in teacherIDs
                            where t.teacherID == tid
                            select t);
            return teachers;
        }

        public void UpdateTeacher(int id)
        {
            // TODO later if we have time
        }

        public void DeleteTeacher(int id)
        {
            // TODO later if we have time
        }

        #endregion

        #region Levels

        /*  Level repository  */
        public IQueryable<Level> Levels()
        {
            return db.levels;
        }

        public IEnumerable<Level> GetLevels()
        {
            var result = from t in db.levels
                         orderby t.title ascending
                         select t;
            return result;
        }

        public Level GetLevel(int id)
        {
            var result = (from t in db.levels
                          where t.levelID == id
                          select t).SingleOrDefault();
            return result;
        }

        public IEnumerable<Level> GetLevels(int id)
        {
            return db.levels;
        }

        public void AddLevel(Level theLevel)
        {
            db.levels.Add(theLevel);
        }

        public IEnumerable<Level> GetAllLevelsByCourse(Course theCourse)
        {
            var levels = (from l in db.levels
                          where l.courseID == theCourse.courseID
                          select l);
            return levels;
        }

        public IEnumerable<Level> GetAllLevelsByCourse(int theCourseID)
        {
            var levels = (from l in db.levels
                          where l.courseID == theCourseID
                          select l);
            return levels;
        }

        public IEnumerable<Level> GetUnlockedLevelsByStudentAndCourse(Student theStudent, Course theCourse)
        {

            var levelIDs = db.studentLevels.Where(s => s.studentID == theStudent.studentID).Select(sl => sl.levelID);

            var levels = (from l in db.levels
                          from le in levelIDs
                          where l.levelID == le && l.courseID == theCourse.courseID
                          orderby l.number descending
                          select l);

            return levels;
        }

        public void UpdateLevel(int id)
        {
            throw new NotImplementedException();
        }

        public void DeleteLevel(int id)
        {
            throw new NotImplementedException();
        }


        #endregion

        #region Exams

        public IQueryable<Exam> Exams()
        {
            return db.exams;
        }

        public IEnumerable<Exam> GetExams()
        {
            var result = from t in db.exams
                         orderby t.title ascending
                         select t;
            return result;
        }

        public Exam GetExams(int id)
        {
            var result = (from t in db.exams
                          where t.examID == id
                          select t).SingleOrDefault();
            return result;
        }

        public void AddExam(Exam theExam)
        {
            db.exams.Add(theExam);
        }

        public IEnumerable<Exam> GetExam(int id)
        {
            return db.exams;
        }

        public IEnumerable<Exam> GetExamsByLevel(Level theLevel)
        {
            var exams = (from e in db.exams
                         where e.levelID == theLevel.levelID
                         select e);

            return exams;
        }

        public Exam GetExamByLevel(Level theLevel)
        {
            var exam = (from e in db.exams
                        where e.levelID == theLevel.levelID
                        select e).SingleOrDefault();
            return exam;
        }

        public Exam GetExamByLevelID(int id)
        {
            var exam = (from e in db.exams
                               where e.levelID == id
                               select e).SingleOrDefault();
            return exam;
        
        }

        public void UpdateExam(Exam theExam)
        {
            var examToUpdate = (from e in db.exams
                                      where e.levelID == theExam.levelID && e.examID == theExam.examID
                                      select e).SingleOrDefault();

      
            examToUpdate.title = theExam.title;
            examToUpdate.description = theExam.description;
            examToUpdate.timeLimit = theExam.timeLimit;


            db.SaveChanges();
        }

        public void DeleteExam(int id)
        {
            throw new NotImplementedException();
        }


        #endregion

        #region Assignments

        public IQueryable<Assignment> Assignments()
        {
            return db.assignments;
        }

        public IEnumerable<Assignment> GetAssignments()
        {
            var result = from t in db.assignments
                         orderby t.title ascending
                         select t;
            return result;
        }

        public Assignment GetAssignmentByID(int id)
        {
            var result = (from t in db.assignments
                          where t.assignmentID == id
                          select t).SingleOrDefault();
            return result;
        }


        public void addAssignment(Assignment theAssignment)
        {
            db.assignments.Add(theAssignment);
        }

        public IEnumerable<Assignment> getAssignments()
        {
            return db.assignments.AsEnumerable();
        }

        public IEnumerable<Assignment> getAssignments(int id)
        {
            var result = from ass in db.assignments
                         where ass.assignmentID < id
                         select ass;
            return result;
        }

        public IEnumerable<Assignment> GetAssignmentsByLevel(Level theLevel)
        {
            var assignments = (from a in db.assignments
                               where a.levelID == theLevel.levelID
                               select a);
            return assignments;
        }

        public Assignment GetAssignmentByLevel(Level theLevel)
        {
            var assignment = (from a in db.assignments
                              where a.levelID == theLevel.levelID
                              select a).SingleOrDefault();
            return assignment;
        }

        public Assignment GetAssignmentByLevelID(int id)
        {
            var assignment = (from a in db.assignments
                              where a.levelID == id
                              select a).SingleOrDefault();
            return assignment;
        }

        public IEnumerable<Assignment> GetAssignmentsByLevel(int theLevelID)
        {
            var assignments = (from a in db.assignments
                               where a.levelID == theLevelID
                               select a);
            return assignments;
        }

        public IEnumerable<Assignment> GetAssignmentByCourseID(int theCourseID)
        {
            var levels = from l in db.levels
                         where l.courseID == theCourseID
                         select l;
            List<Assignment> assignments = new List<Assignment>();
            foreach (var level in levels)
            {
                var assignmentToAdd = (from a in db.assignments
                                   where a.levelID == level.levelID
                                   select a).SingleOrDefault();
                assignments.Add(assignmentToAdd);
            }

            return assignments;
        }


        public int getAssignmentXP(int id)
        {
            //we don't have XP represented anywhere in the database so far - eiki, on monday morning
            throw new NotImplementedException();
        }
        /*
            public void addAssignment(Assignment theAssignment)
            {

                db.assignments.Add(theAssignment);
            }
        */
        public void UpdateAssignment(Assignment theAssignment)
        {
            var assignmentToUpdate = (from a in db.assignments
                                     where a.levelID == theAssignment.levelID && a.assignmentID == theAssignment.assignmentID
                                     select a).SingleOrDefault();

                assignmentToUpdate.title = theAssignment.title;
                assignmentToUpdate.description = theAssignment.description;     
                assignmentToUpdate.timeLimit = theAssignment.timeLimit;
                assignmentToUpdate.file = theAssignment.file;


            db.SaveChanges();
        }

        public void deleteAssignment(int id)
        {
            throw new NotImplementedException();
        }


        #endregion

        #region Lectures

        public IQueryable<Lecture> Lectures()
        {
            return db.lectures;
        }

        public IEnumerable<Lecture> GetLectures()
        {
            var result = from t in db.lectures
                         orderby t.title ascending
                         select t;
            return result;
        }

        //not sure we're going to be using this. we already have a function with the same name that returns an IEnumerable<Lecture> -eiki
        public Lecture GetLectures(int id)
        {
            var result = (from t in db.lectures
                          where t.lectureID == id
                          select t).SingleOrDefault();
            return result;
        }

        public void AddLecture(Lecture theLecture)
        {
            db.lectures.Add(theLecture);
        }

        IEnumerable<Lecture> IGameSchoolRepository.GetLectures(int id)
        {
            var result = from l in db.lectures
                         where l.lectureID <= id
                         select l;
            return result;
        }

        public IEnumerable<Lecture> GetLecturesByLevel(Level theLevel)
        {
            var lectures = (from l in db.lectures
                            where l.levelID == theLevel.levelID
                            select l);
            return lectures;
        }

        public Lecture GetLectureByLevel(Level theLevel)
        {
            var lecture = (from l in db.lectures
                           where l.levelID == theLevel.levelID
                           select l).FirstOrDefault();
            return lecture;
        }

        public Lecture GetLectureByID(int id)
        {
            var lecture = (from l in db.lectures
                           where l.lectureID == id
                           select l).SingleOrDefault();
            return lecture;
        }

        public Lecture GetLectureByLevelID(int id)
        {
            var lecture = (from l in db.lectures
                           where l.levelID == id
                           select l).FirstOrDefault();
            return lecture;
        }

        public void UpdateLecture(Lecture theLecture)
        {
            var lectureToUpdate = (from l in db.lectures
                                   where l.levelID == theLecture.levelID && l.lectureID == theLecture.lectureID
                                   select l).SingleOrDefault();

            lectureToUpdate.title = theLecture.title;
            lectureToUpdate.description = theLecture.description;
            lectureToUpdate.likeAmountToGainXP = theLecture.likeAmountToGainXP;
            lectureToUpdate.allowApproval = theLecture.allowApproval;
            lectureToUpdate.approvalXPAmount = theLecture.approvalXPAmount;
            lectureToUpdate.likeXPAmount = theLecture.likeXPAmount;
            lectureToUpdate.lectureFile = theLecture.lectureFile;

            db.SaveChanges();
        }

        public void DeleteLecture(int id)
        {
            throw new NotImplementedException();
        }

        #endregion

        #region Questions

        public IQueryable<Question> Questions()
        {
            return db.questions;
        }

        public IEnumerable<Question> GetQuestions()
        {
            var result = from t in db.questions
                         orderby t.question ascending //Check this condition - Oli.
                         select t;
            return result;
        }

        public Question GetQuestions(int id)
        {
            var result = (from t in db.questions
                          where t.questionID == id
                          select t).SingleOrDefault();
            return result;
        }

        public void AddQuestion(Question theQuestion)
        {
            db.questions.Add(theQuestion);
        }

        IEnumerable<Question> IGameSchoolRepository.GetQuestions(int id)
        {
            var result = from q in db.questions
                         where q.questionID <= id
                         select q;
            return result;
        }

        public void UpdateQuestion(int id)
        {
            throw new NotImplementedException();
        }

        public void DeleteQuestion(int id)
        {
            throw new NotImplementedException();
        }

        #endregion

        #region Answers

        public IQueryable<Answer> Answers()
        {
            return db.answers;
        }

        public IEnumerable<Answer> GetAnswers()
        {
            var result = from t in db.answers
                         orderby t.answer ascending //check this condition - Oli.
                         select t;
            return result;
        }

        public Answer GetAnswers(int id)
        {
            var result = (from t in db.answers
                          where t.answerID == id
                          select t).SingleOrDefault();
            return result;
        }

        public void AddAnswer(Answer theAnswer)
        {
            db.answers.Add(theAnswer);
        }

        IEnumerable<Answer> IGameSchoolRepository.GetAnswers(int id)
        {
            var result = from a in db.answers
                         where a.answerID <= id
                         select a;
            return result;
        }

        public void UpdateAnswer(int id)
        {
            throw new NotImplementedException();
        }

        public void DeleteAnswer(int id)
        {
            throw new NotImplementedException();
        }


        #endregion

        #region Comments

        public IQueryable<Comment> Comments()
        {
            return db.comments;
        }

        public IEnumerable<Comment> GetComments()
        {
            var result = from t in db.comments
                         //orderby t.lecture ascending //Check this condition - Oli.
                         select t;
            return result;
        }

        public Comment GetComments(int id)
        {
            var result = (from t in db.comments
                          where t.commentID == id
                          select t).SingleOrDefault();
            return result;
        }

        public void AddComment(Comment theComment)
        {
            db.comments.Add(theComment);
        }

        IEnumerable<Comment> IGameSchoolRepository.GetComments(int id)
        {
            var result = from r in db.comments
                         where r.commentID <= id
                         select r;
            return result;
        }

        public void UpdateComment(int id)
        {
            throw new NotImplementedException();
        }

        public void DeleteComment(int id)
        {
            throw new NotImplementedException();
        }



        #endregion

        #region Likes

        public IQueryable<Like> Likes()
        {
            return db.likes;
        }


        public IEnumerable<Like> GetLikes()
        {
            var result = from t in db.likes
                         orderby t.comment ascending //Check this condition - Oli.
                         select t;
            return result;
        }

        public Like GetLikes(int id)
        {
            var result = (from t in db.likes
                          where t.likeID == id
                          select t).SingleOrDefault();
            return result;
        }

        public void AddLike(Like theLike)
        {
            db.likes.Add(theLike);
        }

        int IGameSchoolRepository.GetLikes(int commentID)
        {
            var result = from l in db.likes
                         where l.commentID == commentID
                         select l;
            return 99;
            // return result.Sum();
        }

        public void AddLike(int studentID, int commentID)
        {

            throw new NotImplementedException();
        }


        public void Save()
        {
            db.SaveChanges();
        }

        #endregion


        #region TeacherCourses

        public IQueryable<TeacherCourse> TeacherCourses() // Ólöf og Óli
        {
            return db.teacherCourses;
        }

        public void AddTeacherCourse(int theCourseID, int theTeacherID) // Ólöf og Óli
        {
            TeacherCourse tc = new TeacherCourse();
            tc.courseID = theCourseID;
            tc.teacherID = theTeacherID;
            db.teacherCourses.Add(tc);
        }

        public TeacherCourse GetTeacherCourse(int theCourseID, int theTeacherID) // Ólöf og Óli
        {
            var result = (from tc in db.teacherCourses
                          where tc.courseID == theCourseID && tc.teacherID == theTeacherID
                          select tc).SingleOrDefault();
            return result;
        }

        #endregion

        #region StudentCourses

        public IQueryable<StudentCourse> StudentCourses() // Ólöf og Óli
        {
            return db.studentCourses;
        }

        public void AddStudentCourse(int theCourseID, int theStudentID) // Ólöf og Óli
        {
            StudentCourse sc = new StudentCourse();
            sc.courseID = theCourseID;
            sc.studentID = theStudentID;
            db.studentCourses.Add(sc);
        }

        public StudentCourse GetStudentCourse(int theCourseID, int theStudentID) // Ólöf og Óli
        {
            var result = (from sc in db.studentCourses
                          where sc.courseID == theCourseID && sc.studentID == theStudentID
                          select sc).SingleOrDefault();
            return result;
        }

        public void UpdateStudentXP(StudentCourse theStudentCourse)
        {
            var studentCourseToUpdate = (from sc in db.studentCourses
                                     where sc.studentID == theStudentCourse.studentID && sc.courseID == theStudentCourse.courseID
                                     select sc).SingleOrDefault();

            studentCourseToUpdate.studentXP = theStudentCourse.studentXP;

            db.SaveChanges();
        }
        #endregion

        #region CourseAchievement

        public void AddCourseAchievement(Course theCourse, Achievement theAchievement)
        {
            CourseAchievement ca = new CourseAchievement
            {
                courseID = theCourse.courseID,
                achievementID = theAchievement.achievementID
            };

            db.courseAchievements.Add(ca);
        }
        #endregion

        //goal: to get XP of student above me in the course
        // get list of all students in the course
        // 
        public int getXPofStudentAbove(int theCourseID, int theStudentID)
        {
            /*
                        var xpRoster = from f in db.studentCourses
                                  where f.courseID == theCourseID
                                  orderby f.studentXP ascending
                                  select f;
             */
            var ourStudentXP = db.studentCourses.Where(c => c.studentID == theStudentID && c.courseID == theCourseID).Select(c => c.studentXP).SingleOrDefault();

            var xpRoster = db.studentCourses.Where(c => c.courseID == theCourseID).OrderBy(c => c.studentXP).Select(sc => sc.studentXP);

            var studentPosition = (from b in xpRoster
                                   where b > ourStudentXP
                                   select b).FirstOrDefault();

            return studentPosition;
        }

        public int getXPofStudentBelow(int theCourseID, int theStudentID)
        {
            /*
                        var xpRoster = from f in db.studentCourses
                                  where f.courseID == theCourseID
                                  orderby f.studentXP ascending
                                  select f;
             */
            var ourStudentXP = db.studentCourses.Where(c => c.studentID == theStudentID && c.courseID == theCourseID).Select(c => c.studentXP).SingleOrDefault();

            var xpRoster = db.studentCourses.Where(c => c.courseID == theCourseID).OrderByDescending(c => c.studentXP).Select(sc => sc.studentXP);

            var studentPosition = (from b in xpRoster
                                   where b < ourStudentXP
                                   select b).FirstOrDefault();

            return studentPosition;
        }

        public void addStudentAssignment(StudentAssignment stuAss)
        {
            db.studentAssignments.Add(stuAss);
        }

        public IQueryable<StudentAssignment> getStudentAssignmentsByCourse(int courseID)
        {
            var allHandedIns = from x in db.studentAssignments
                where x.handedInTime != null
                orderby x.handedInTime descending
                select x;

            var studentsInThisCourse = from s in db.studentCourses
                                       where s.courseID == courseID
                                       select s;

            var handinsByCourse = from a in allHandedIns
                                  from s in studentsInThisCourse
                                  where a.studentID == s.studentID
                                  select a;

            return handinsByCourse;
        }

       // public List<StudentAssignment> getUn


        public void UpdateLecture(int id)
        {
            throw new NotImplementedException();
        }

    }
}